/**
 * Module dependencies.
 */


//导入express
const express = require('express');
//导入path
const path = require('path');
//导入pg
const pg = require('pg');

const { Pool } = require('pg');

//导入cors
const cors = require('cors');
const { copyFileSync } = require('fs');
const port = 83;
//创建应用对象
const app = express();
app.use(cors());
app.use(express.json());
app.use(express.urlencoded({extended: false}));
// // // 静态资源配置
app.use(express.static(path.join(__dirname, 'dist')));
app.get('*', function(req, res) {
  res.sendFile(path.join(__dirname, 'dist', 'index.html'));
});
// 创建 PostgreSQL 数据库连接池
const pool = new Pool({
    user: 'postgres',
    host: 'localhost',
    database: 'postgres',
    password: '123456',
    port: 5432, // 默认 PostgreSQL 端口
  });
  // 监听连接池的 connect 事件
pool.on('connect', () => {
  console.log('Connected to PostgreSQL database');
});

// 监听连接池的 error 事件
pool.on('error', (err) => {
  console.error('Error connecting to PostgreSQL database:', err);
});

// 处理请求的路由
app.post('/query', async (req, res) => {
  const { area, sensor, types, dateRange, drawnFeatures} = req.body;
  // console.log('length:'+ types.length)
  // 构建 SQL 查询语句
  let sqlQuery = 'SELECT gid, productid, time, pos, types FROM gf';
  const conditions = [];

  // 添加条件：如果 area 不为空，添加 area 条件
  if (area) {
    // 查询匹配的行
    const areaQuery = `SELECT geom FROM guizhou_area WHERE city = '${area}'`;
    const areaResult = await pool.query(areaQuery);
    if(areaResult.rows.length > 0){
      console.log(areaResult.rows.length);
      // 获取匹配行的 geom
      const areaGeom = areaResult.rows[0].geom;

      // 添加空间查询条件
      conditions.push(`ST_Intersects(gf.geom, '${areaGeom}')`);
    }
  }

  // 添加条件：如果 sensor 不为空，添加 sensor 条件
  if (sensor) {
    conditions.push(`satellite IN ('${sensor.join("','")}')`);
  }

  // 添加条件：如果 types 不为空，添加 types 条件
  if (types) {
      // 使用 IN 操作符，只要匹配其中一个值即可
      conditions.push(`types IN ('${types.join("','")}')`);
  }

  // 添加条件：如果 dateRange 不为空，添加日期范围条件
  if (dateRange && dateRange.length >= 2) {
    const startDate = dateRange[0];
    const endDate = dateRange[1];
  
    conditions.push(`time >= '${startDate}' AND time <= '${endDate}'`);
  }
 // 添加空间查询条件（仅当 drawnFeatures 存在时）
 if (drawnFeatures && drawnFeatures.length > 0) {
  const coordinates = drawnFeatures[0].coordinates; // 获取 coordinates 数组
  const type = drawnFeatures[0].type; // 获取 type 字符串
  const radius= drawnFeatures[0].radius; // 获取 radius 字符串

  // 根据具体的坐标和类型信息构建空间查询条件
  if (type === 'Polygon' && coordinates && coordinates.length > 0) {
    const geometry = {
      type: 'Polygon',
      coordinates: coordinates,
    };
    addSpatialCondition('Polygon', geometry, 4326, conditions); // 将 conditions 作为参数传递
  } else if (type === 'Circle' && coordinates && coordinates.length > 0) {
    const geometry = {
      type: 'Circle',
      coordinates: coordinates,
      radius:radius
    };
    addSpatialCondition('Circle', geometry, 4326, conditions); // 将 conditions 作为参数传递
  } else if (type === 'LineString' && coordinates && coordinates.length > 0) {
    const geometry = {
      type: 'LineString',
      coordinates: coordinates,
    };
    addSpatialCondition('LineString', geometry, 4326, conditions); // 将 conditions 作为参数传递
  } else if (type === 'Point' && coordinates && coordinates.length > 0) {
    const geometry = {
      type: 'Point',
      coordinates: coordinates,
    };
    addSpatialCondition('Point', geometry, 4326, conditions); // 将 conditions 作为参数传递
  }
}


  // 将条件连接为 AND 关系
  if (conditions.length > 0) {
    sqlQuery += ' WHERE ' + conditions.join(' AND ');
  }
  // 添加排序条件
  sqlQuery += ' ORDER BY satellite ASC, time DESC';
  // console.log('查询语句为：' + sqlQuery)
  console.log('--------------------')
  try {
    // 执行查询
    const result = await pool.query(sqlQuery);

    // 返回查询结果给前端
    res.json(result.rows);
  } catch (error) {
    console.error('Error executing SQL query:', error);
    res.status(500).send('Internal Server Error');
  }
});
 // 在条件构建函数中接受 conditions 作为参数
function addSpatialCondition(geometryType, geometry, targetSRID, conditions) {
  let spatialCondition = '';

  switch (geometryType) {
    case 'Polygon':
      spatialCondition = buildSpatialConditionForPolygon(geometry, targetSRID);
      break;
    case 'Circle':
      spatialCondition = buildSpatialConditionForCircle(geometry, targetSRID);
      break;
    case 'LineString':
      spatialCondition = buildSpatialConditionForLineString(geometry, targetSRID);
      break;
    case 'Point':
      spatialCondition = buildSpatialConditionForPoint(geometry, targetSRID);
      break;
    // 添加其他几何类型的处理逻辑
  }

  if (spatialCondition) {
    conditions.push(spatialCondition);
  }
}

  // 辅助函数，构建 Polygon 类型的空间查询条件
  function buildSpatialConditionForPolygon(geometry, targetSRID) {
    const transformedGeometry = `ST_Transform(ST_SetSRID(ST_GeomFromGeoJSON('${JSON.stringify(geometry)}'), ${targetSRID}), ${targetSRID})`;
    return `ST_Intersects(geom, ${transformedGeometry})`;
  }
  // 添加处理 LineString 类型的空间条件的函数
  function buildSpatialConditionForLineString(geometry, targetSRID) {
    const transformedGeometry = `ST_Transform(ST_SetSRID(ST_GeomFromGeoJSON('${JSON.stringify(geometry)}'), ${targetSRID}), ${targetSRID})`;
    return `ST_Intersects(geom, ${transformedGeometry})`;
  }

  // 添加处理 Point 类型的空间条件的函数
  function buildSpatialConditionForPoint(geometry, targetSRID) {
    const transformedGeometry = `ST_Transform(ST_SetSRID(ST_GeomFromGeoJSON('${JSON.stringify(geometry)}'), ${targetSRID}), ${targetSRID})`;
    return `ST_Intersects(geom, ${transformedGeometry})`;
  }
  // 添加处理 Circle 类型的空间条件的函数
  function buildSpatialConditionForCircle(geometry, targetSRID) {
    // 获取圆心坐标和半径
    const centerCoordinates = geometry.coordinates;
    const radius = geometry.radius;
  // 构建空间查询条件，使用 ST_DWithin 函数
  console.log('coordinates:' + centerCoordinates)
  const transformedGeometry = `ST_Transform(ST_SetSRID(ST_GeomFromGeoJSON('${JSON.stringify({ type: 'Point', coordinates: centerCoordinates })}'), ${targetSRID}), ${targetSRID})`;
  return `ST_DWithin(geom, ${transformedGeometry}, ${radius})`;
  }

  app.listen(port, () => {
    console.log(`Server is running on port ${port}`);
  });

